package cn.ywang.spring.orm.framework;

import cn.ywang.spring.orm.utils.GenericUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import javax.sql.DataSource;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * BaseDao 扩展类,主要功能是支持自动拼装sql语句，必须继承方可使用
 *
 * @author BeatificWang
 * @date 2019/5/4 23:41
 */
public abstract class BaseDaoSupport<T extends Serializable, PK extends Serializable> {
	private Logger log = Logger.getLogger(BaseDaoSupport.class);

	private String tableName = "";

	private JdbcTemplate jdbcTemplateWrite;
	private JdbcTemplate jdbcTemplateReadOnly;

	private DataSource dataSourceReadOnly;
	private DataSource dataSourceWrite;

	private EntityOperation<T> op;

	protected BaseDaoSupport() {
		try {
			Class<T> entityClass = GenericUtils.getSuperClassGenricType(this.getClass(), 0);
			this.op = new EntityOperation<T>(entityClass, this.getPKColumn());
			this.setTableName(this.op.tableName);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	protected String getTableName() {
		return this.tableName;
	}

	protected DataSource getDataSourceReadOnly() {
		return this.dataSourceReadOnly;
	}

	protected DataSource getDataSourceWrite() {
		return this.dataSourceWrite;
	}

	/**
	 * 动态切换表名
	 */
	protected void setTableName(String tableName) {
		if (StringUtils.isEmpty(tableName)) {
			this.tableName = this.op.tableName;
		}
		else {
			this.tableName = tableName;
		}
	}

	protected void setDataSourceWrite(DataSource dataSourceWrite) {
		this.dataSourceWrite = dataSourceWrite;
		this.jdbcTemplateWrite = new JdbcTemplate(dataSourceWrite);
	}

	protected void setDataSourceReadOnly(DataSource dataSourceReadOnly) {
		this.dataSourceReadOnly = dataSourceReadOnly;
		this.jdbcTemplateReadOnly = new JdbcTemplate(dataSourceReadOnly);
	}

	private JdbcTemplate jdbcTemplateReadOnly() {
		return this.jdbcTemplateReadOnly;
	}

	private JdbcTemplate jdbcTemplateWrite() {
		return this.jdbcTemplateWrite;
	}

	/**
	 * 将对象解析为Map
	 *
	 * @param entity
	 * @return
	 */
	protected Map<String, Object> parse(T entity) {
		return this.op.parse(entity);
	}


	/**
	 * 插入并返回id
	 *
	 * @param entity
	 * @return
	 */
	protected PK insertAndReturnId(T entity) throws Exception {
		return (PK) this.doInsertReturnKey(this.parse(entity));
	}

	/**
	 * 插入一条记录
	 *
	 * @param entity
	 * @return
	 */
	protected boolean insert(T entity) throws Exception {
		return this.doInsert(this.parse(entity));
	}


	/**
	 * 更新对象.<br>
	 * 例如：以下代码将对象更新到数据库
	 * <pre>
	 * 		<code>
	 * User entity = service.get(1);
	 * entity.setName(&quot;zzz&quot;);
	 * // 更新对象
	 * service.update(entity);
	 * </code>
	 * </pre>
	 *
	 * @param entity 待更新对对象
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 */
	protected boolean update(T entity) throws Exception {
		return this.doUpdate(this.op.pkField.get(entity), this.parse(entity)) > 0;
	}

	/**
	 * 删除对象.<br>
	 * 例如：以下删除entity对应的记录
	 * <pre>
	 * 		<code>
	 * service.remove(entity);
	 * </code>
	 * </pre>
	 *
	 * @param entity 待删除的实体对象
	 */
	protected boolean delete(T entity) throws Exception {
		return this.doDelete(this.op.pkField.get(entity)) > 0;
	}

	/**
	 * 查询函数，使用查询规
	 * 例如以下代码查询条件为匹配的数据
	 *
	 * <pre>
	 * 		<code>
	 * QueryRule queryRule = QueryRule.getInstance();
	 * queryRule.addLike(&quot;username&quot;, user.getUsername());
	 * queryRule.addLike(&quot;monicker&quot;, user.getMonicker());
	 * queryRule.addBetween(&quot;id&quot;, lowerId, upperId);
	 * queryRule.addDescOrder(&quot;id&quot;);
	 * queryRule.addAscOrder(&quot;username&quot;);
	 * list = userService.find(User.class, queryRule);
	 * </code>
	 * </pre>
	 *
	 * @param queryRule 查询规则
	 * @return 查询出的结果List
	 */
	protected List<T> select(QueryRule queryRule) throws Exception {
		QueryRuleSqlBuilder bulider = new QueryRuleSqlBuilder(queryRule);
		String ws = this.removeFirstAnd(bulider.getWhereSql());
		String whereSql = ("".equals(ws) ? ws : (" where " + ws));
		String sql = "select " + this.op.allColumn + " from " + this.getTableName() + whereSql;
		Object[] values = bulider.getValues();
		String orderSql = bulider.getOrderSql();
		orderSql = (StringUtils.isEmpty(orderSql) ? " " : (" order by " + orderSql));
		sql += orderSql;
		this.log.debug(sql);
		return (List<T>) this.jdbcTemplateReadOnly().query(sql, this.op.rowMapper, values);
	}


	private String removeFirstAnd(String sql) {
		if (StringUtils.isEmpty(sql)) {
			return sql;
		}
		return sql.trim().toLowerCase().replaceAll("^\\s*and", "") + " ";
	}

	/**
	 * 生成简单对象UPDATE语句，简化sql拼接
	 *
	 * @param tableName
	 * @param pkName
	 * @param pkValue
	 * @param params
	 * @return
	 */
	private String makeSimpleUpdateSql(String tableName, String pkName, Object pkValue, Map<String, Object> params) {
		if (StringUtils.isEmpty(tableName) || params == null || params.isEmpty()) {
			return "";
		}

		StringBuffer sb = new StringBuffer();
		sb.append("update ").append(tableName).append(" set ");
		//添加参数
		Set<String> set = params.keySet();
		int index = 0;
		for (String key : set) {
			//			 sb.append(key).append(" = :").append(key);
			sb.append(key).append(" = ?");
			if (index != set.size() - 1) {
				sb.append(",");
			}
			index++;
		}
		//		sb.append(" where ").append(pkName).append(" = :").append(pkName) ;
		sb.append(" where ").append(pkName).append(" = ?");
		params.put("where_" + pkName, params.get(pkName));

		return sb.toString();
	}


	/**
	 * 生成对象INSERT语句，简化sql拼接
	 *
	 * @param tableName
	 * @param params
	 * @return
	 */
	private String makeSimpleInsertSql(String tableName, Map<String, Object> params) {
		if (StringUtils.isEmpty(tableName) || params == null || params.isEmpty()) {
			return "";
		}
		StringBuffer sb = new StringBuffer();
		sb.append("insert into ").append(tableName);

		StringBuffer sbKey = new StringBuffer();
		StringBuffer sbValue = new StringBuffer();

		sbKey.append("(");
		sbValue.append("(");
		//添加参数
		Set<String> set = params.keySet();
		int index = 0;
		for (String key : set) {
			sbKey.append(key);
			//			sbValue.append(" :").append(key);
			sbValue.append(" ?");
			if (index != set.size() - 1) {
				sbKey.append(",");
				sbValue.append(",");
			}
			index++;
		}
		sbKey.append(")");
		sbValue.append(")");

		sb.append(sbKey).append("VALUES").append(sbValue);

		return sb.toString();
	}

	/**
	 * 生成对象INSERT语句，简化sql拼接
	 *
	 * @param tableName
	 * @param params
	 * @return
	 */
	private String makeSimpleInsertSql(String tableName, Map<String, Object> params, List<Object> values) {
		if (StringUtils.isEmpty(tableName) || params == null || params.isEmpty()) {
			return "";
		}
		StringBuffer sb = new StringBuffer();
		sb.append("insert into ").append(tableName);

		StringBuffer sbKey = new StringBuffer();
		StringBuffer sbValue = new StringBuffer();

		sbKey.append("(");
		sbValue.append("(");
		//添加参数
		Set<String> set = params.keySet();
		int index = 0;
		for (String key : set) {
			sbKey.append(key);
			sbValue.append(" ?");
			if (index != set.size() - 1) {
				sbKey.append(",");
				sbValue.append(",");
			}
			index++;
			values.add(params.get(key));
		}
		sbKey.append(")");
		sbValue.append(")");

		sb.append(sbKey).append("VALUES").append(sbValue);

		return sb.toString();
	}


	private Serializable doInsertReturnKey(Map<String, Object> params) {
		final List<Object> values = new ArrayList<Object>();
		final String sql = this.makeSimpleInsertSql(this.getTableName(), params, values);
		KeyHolder keyHolder = new GeneratedKeyHolder();
		final JdbcTemplate jdbcTemplate = new JdbcTemplate(this.getDataSourceWrite());
		try {

			jdbcTemplate.update(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(

						Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

					for (int i = 0; i < values.size(); i++) {
						ps.setObject(i + 1, values.get(i) == null ? null : values.get(i));

					}
					return ps;
				}

			}, keyHolder);
		} catch (DataAccessException e) {
			this.log.error("error", e);
		}


		if (keyHolder == null) {
			return "";
		}


		Map<String, Object> keys = keyHolder.getKeys();
		if (keys == null || keys.size() == 0 || keys.values().size() == 0) {
			return "";
		}
		Object key = keys.values().toArray()[0];
		if (key == null || !(key instanceof Serializable)) {
			return "";
		}
		if (key instanceof Number) {
			//Long k = (Long) key;
			Class clazz = key.getClass();
			//			return clazz.cast(key);
			return (clazz == int.class || clazz == Integer.class) ? ((Number) key).intValue() : ((Number) key).longValue();


		}
		else if (key instanceof String) {
			return (String) key;
		}
		else {
			return (Serializable) key;
		}


	}


	/**
	 * 生成默认的对象UPDATE语句，简化sql拼接
	 *
	 * @param pkValue
	 * @param params
	 * @return
	 */
	private String makeDefaultSimpleUpdateSql(Object pkValue, Map<String, Object> params) {
		return this.makeSimpleUpdateSql(this.getTableName(), this.getPKColumn(), pkValue, params);
	}


	/**
	 * 删除实例对象，返回删除记录数
	 *
	 * @param tableName
	 * @param pkName
	 * @param pkValue
	 * @return
	 */
	private int doDelete(String tableName, String pkName, Object pkValue) {
		StringBuffer sb = new StringBuffer();
		sb.append("delete from ").append(tableName).append(" where ").append(pkName).append(" = ?");
		int ret = this.jdbcTemplateWrite().update(sb.toString(), pkValue);
		return ret;
	}

	/**
	 * 删除默认实例对象，返回删除记录数
	 *
	 * @param pkValue
	 * @return
	 */
	private int doDelete(Object pkValue) {
		return this.doDelete(this.getTableName(), this.getPKColumn(), pkValue);
	}

	/**
	 * 更新实例对象，返回删除记录数
	 *
	 * @param pkValue
	 * @param params
	 * @return
	 */
	private int doUpdate(Object pkValue, Map<String, Object> params) {
		//
		String sql = this.makeDefaultSimpleUpdateSql(pkValue, params);
		params.put(this.getPKColumn(), pkValue);
		int ret = this.jdbcTemplateWrite().update(sql, params.values().toArray());
		return ret;
	}

	/**
	 * 插入
	 *
	 * @param params
	 * @return
	 */
	private boolean doInsert(Map<String, Object> params) {
		String sql = this.makeSimpleInsertSql(this.getTableName(), params);
		int ret = this.jdbcTemplateWrite().update(sql, params.values().toArray());
		return ret > 0;
	}

	/**
	 * 获取主键列名称 建议子类重写
	 *
	 * @return
	 */
	protected abstract String getPKColumn();

	protected abstract void setDataSource(DataSource dataSource);


}
